/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import Domain.WaitingListEntry;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Min0
 */
public class DBWaitingList implements IFDBWaitingList
{
    private Connection con;
    //private ArrayList<WaitingListEntry> WaitingList = new ArrayList<WaitingListEntry>();

    public DBWaitingList()
    {
        con = DBConnection.getInstance().getDBcon();
    }

    //START private methods for class
    private WaitingListEntry BuildWaitingListEntry(ResultSet results)
    {
        WaitingListEntry appObj = new WaitingListEntry();
        try
        {
            appObj.setID(results.getInt("ID"));
            appObj.setActivityID(results.getString("ActivityID"));
            appObj.setSportFacility(results.getString("SportFacility"));
            appObj.setDate(results.getString("Date"));
            appObj.setTime(results.getString("Time"));
            appObj.setCustomerID(results.getString("CustomerID"));
        } 
        catch (Exception e)
        {
            System.out.println("error in building the WaitingListEntry object\n" + e.getMessage());
        }
        return appObj;
    }

    private String BuildQuery(String wClause)
    {
        String query = "SELECT * FROM WaitingList";

        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    private WaitingListEntry singleWhere(String wClause)
    {
        ResultSet results;
        WaitingListEntry appObj = new WaitingListEntry();
        String query = BuildQuery(wClause);
        System.out.println(query);
        try
        {
            java.sql.Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next())
            {
                appObj = BuildWaitingListEntry(results);
                //assocaition is to be build
                stmt.close();
            }

        }//end try	
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return appObj;
    }

    private ArrayList<WaitingListEntry> miscWhere(String wClause)
    {
        ResultSet results;
        ArrayList<WaitingListEntry> list = new ArrayList<WaitingListEntry>();

        String query = BuildQuery(wClause);

        try
        { // read the WaitingList from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next())
            {
                WaitingListEntry appObj = new WaitingListEntry();
                appObj = BuildWaitingListEntry(results);
                list.add(appObj);
            }//end while
            stmt.close();
        } catch (Exception e)
        {
            System.out.println("Query exception - select: " + e);
        }
        return list;
    }
    //END private methods for class

    @Override
    public void insertEntry(WaitingListEntry WLE)
    {
        String query = "INSERT INTO WaitingList(ID,ActivityID,SportFacility,Date,Time,CustomerID) VALUES("
                + WLE.getID() + ",'"
                + WLE.getActivityID() + "','"
                + WLE.getSportFacility() + "','"
                + WLE.getDate() + "','"
                + WLE.getTime() + "','"
                + WLE.getCustomerID() + "')";
        
        System.out.println("insert: " + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }//end try	
        catch (Exception ex)
        {
            System.out.println("Insert exception in waiting list db: " + ex);
        }
    }

    @Override
    public void insertEntry(int ID, String ActivityID,String SportFacility, String Date, String Time, String CustomerID)
    {
        String query = "INSERT INTO WaitingList(ID,ActivityID,SportFacility,Date,Time,CustomerID) VALUES("
                + ID + ",'"
                + ActivityID + "','"
                + SportFacility + "','"
                + Date + "','"
                + Time + "','"
                + CustomerID + "')";
        
        System.out.println("insert: " + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }//end try	
        catch (Exception ex)
        {
            System.out.println("Insert exception in waitingList db: " + ex);
        }
    }

    @Override
    public WaitingListEntry getEntry(int ID)
    {
        String wClause = "ID = " + ID;
        return singleWhere(wClause);
    }

    @Override
    public ArrayList<WaitingListEntry> getEntryList()
    {
        return miscWhere("");
    }

    @Override
    public void updateEntry(int ID, String ActivityID, String CustomerID)
    {
        String query = "UPDATE WaitingList SET "
                + "ActivityID = '" + ActivityID + "',"
                + "CustomerID = '" + CustomerID + "'"
                + " WHERE ID = " + ID;
        
        System.out.println("Update query:" + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex)
        {
            System.out.println("Update exception in waitingList db: " + ex);
        }
    }

    @Override
    public void deleteEntry(String ActivityID, String CustomerID)
    {
        String query = "DELETE FROM WaitingList WHERE ActivityID = '"
                + ActivityID + "' AND CustomerID = '"+ CustomerID+"'";
        System.out.println(query);
        try
        { // delete from apartment
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex)
        {
            System.out.println("Delete exception in waitingList db: " + ex);
        }
    }
    
    @Override
    public int getMax()
    {
        int max = getMax.getMaxId("Select max(ID) from WaitingList");
        return max;
    }

    @Override
    public int getID(String ActivityID, String SportFacility, String Date, String Time)
    {
        String query = "ActivityID ='"+ActivityID+"' AND Date = '"+Date+"' AND Time ='"+Time+"'";
        ArrayList<WaitingListEntry> entries = new ArrayList<WaitingListEntry>();
        entries = miscWhere(query);
        return entries.size();
    }

    @Override
    public WaitingListEntry getCustomerEntry(String customerID)
    {
        String wClause = " CustomerID = '"+customerID+"'";
        return singleWhere(wClause);
    }
}
